package cn.liutao.bill.service;

import cn.liutao.bill.vo.*;
import cn.liutao.common.enums.BillEnum;
import cn.liutao.common.enums.CommonEnum;
import cn.liutao.common.enums.OrderEnum;
import com.mybatisflex.core.query.QueryWrapper;
import com.mybatisflex.core.row.Db;
import com.mybatisflex.core.row.Row;
import com.mybatisflex.core.row.RowUtil;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static cn.liutao.dish.entity.po.table.CuisineTableDef.CUISINE;
import static cn.liutao.dish.entity.po.table.DishTableDef.DISH;
import static cn.liutao.dish.entity.po.table.TasteTableDef.TASTE;
import static cn.liutao.order.entity.po.table.CartTableDef.CART;
import static cn.liutao.order.entity.po.table.OrderCartRelationTableDef.ORDER_CART_RELATION;
import static cn.liutao.order.entity.po.table.OrderTableDef.ORDER;
import static com.mybatisflex.core.query.QueryMethods.*;

/**
 * @author liutao
 * @Description 报表业务代码(旧逻辑)
 * @date 2023/11/5
 */
public class BillService_bak {

    private static volatile BillService_bak instance;

    private BillService_bak() {

    }

    public static BillService_bak getInstance() {
        if (instance == null) {
            synchronized (BillService_bak.class) {
                if (instance == null) {
                    instance = new BillService_bak();
                }
            }
        }
        return instance;
    }

    /**
     * 销售总览
     * @return
     */
    public OverallVo getOverall() {
        LocalDateTime now = LocalDateTime.now();
        int month = now.getMonthValue();
        int year = now.getYear();
        int day = now.getDayOfMonth();
        //获取年销量和年销售额
        QueryWrapper qw1 = QueryWrapper.create().select(sum(ORDER.TOTAL_PRICE).as("total_price"),sum(ORDER.TOTAL_QUANTITY).as("total_quantity")).from(ORDER).where(year(ORDER.CREATE_TIME).eq(year)).and(ORDER.STATUS.eq(OrderEnum.ORDER_STATUS_FINISHED.getIntValue()));
        Row row1 = Db.selectOneByQuery(qw1);
        //获取月销量和月销售额
        QueryWrapper qw2 = QueryWrapper.create().select(sum(ORDER.TOTAL_PRICE).as("total_price"),sum(ORDER.TOTAL_QUANTITY).as("total_quantity")).from(ORDER).where(year(ORDER.CREATE_TIME).eq(year)).and(month(ORDER.CREATE_TIME).eq(month)).and(ORDER.STATUS.eq(OrderEnum.ORDER_STATUS_FINISHED.getIntValue()));
        Row row2 = Db.selectOneByQuery(qw2);
        //获取日销量和日销售
        QueryWrapper qw3 = QueryWrapper.create().select(sum(ORDER.TOTAL_PRICE).as("total_price"),sum(ORDER.TOTAL_QUANTITY).as("total_quantity")).from(ORDER).where(year(ORDER.CREATE_TIME).eq(year)).and(month(ORDER.CREATE_TIME).eq(month)).and(day(ORDER.CREATE_TIME).eq(day)).and(ORDER.STATUS.eq(OrderEnum.ORDER_STATUS_FINISHED.getIntValue()));
        Row row3 = Db.selectOneByQuery(qw3);
        //封装数据据
        OverallVo overallVo = new OverallVo();
        if (row1!=null) {
            overallVo.setYearCustom(row1.getBigDecimal("total_price"));
            overallVo.setYearQuantity(row1.getInt("total_quantity"));
        }
        if (row2!=null) {
            overallVo.setMonthCustom(row2.getBigDecimal("total_price"));
            overallVo.setMonthQuantity(row2.getInt("total_quantity"));
        }
        if (row3!=null){
            overallVo.setDayCustom(row3.getBigDecimal("total_price"));
            overallVo.setDayQuantity(row3.getInt("total_quantity"));
        }

        return overallVo;
    }

    /**
     * 菜系销量排行
     * @return
     */
    public List<RankVO> getCuisineRank(Integer orderBy) {
        //子查询
        QueryWrapper children = QueryWrapper.create().select(CUISINE.ID.as("id"), CUISINE.NAME.as("name"), sum(CART.PRICE).as("total_price"), sum(CART.QUANTITY).as("total_quantity")).from(ORDER).as("o")
                .leftJoin(ORDER_CART_RELATION).as("ocr").on(ORDER.ID.eq(ORDER_CART_RELATION.ORDER_ID))
                .leftJoin(CART).as("c").on(CART.ID.eq(ORDER_CART_RELATION.CART_ID))
                .leftJoin(DISH).as("d").on(DISH.ID.eq(CART.DISH_ID))
                .leftJoin(CUISINE).as("cu").on(CUISINE.ID.eq(DISH.CUISINE_ID))
                .where(ORDER.STATUS.eq(OrderEnum.ORDER_STATUS_FINISHED.getIntValue()))
                .and(CART.STATUS.eq(OrderEnum.CART_STATUS_BUY.getIntValue()))
                .and(DISH.STATUS.eq(CommonEnum.STATUS_ON.getIntValue()))
                .and(CUISINE.STATUS.eq(CommonEnum.STATUS_ON.getIntValue()))
                .groupBy("cu.id");
        orderByType(orderBy, children);
        //父查询
        QueryWrapper parent = QueryWrapper.create().select(column("cui.id").as("id"),
                column("cui.name").as("name"),
                ifNull(column("total_price"),column("0")).as("total_custom"),
                ifNull(column("total_quantity"),column("0")).as("total_quantity"))
            .from(CUISINE).as("cui").leftJoin(
                        children
            ).as("children")
            .on(column("children.id").eq(column("cui.id")))
            .where(CUISINE.STATUS.eq(CommonEnum.STATUS_ON.getIntValue()))
            .orderBy(column("total_custom"),false);
            ;
        String sql = parent.toSQL();
        List<Row> rows = Db.selectListByQuery(parent);
        List<RankVO> cuisineList = RowUtil.toObjectList(rows, RankVO.class);
        return cuisineList;
    }

    /**
     * 根据参数构造筛选条件
     * @param orderBy
     * @param qw
     */
    private static void orderByType(Integer orderBy, QueryWrapper qw) {
        //根据参数筛选
        switch (BillEnum.getByValue(orderBy)){
            case BILL_ORDER_BY_YEAR:
                qw.and(year(ORDER.CREATE_TIME).eq(year(now())));
                break;
            case BILL_ORDER_BY_MONTH:
                qw.and(year(ORDER.CREATE_TIME).eq(year(now()))).and(month(ORDER.CREATE_TIME).eq(month(now())));
                break;
            case BILL_ORDER_BY_DAY:
                qw.and(year(ORDER.CREATE_TIME).eq(year(now()))).and(month(ORDER.CREATE_TIME).eq(month(now()))).and(day(ORDER.CREATE_TIME).eq(day(now())));
                break;
            case BILL_ORDER_BY_WEEK:
                qw.and(year(ORDER.CREATE_TIME).eq(year(now()))).and(month(ORDER.CREATE_TIME).eq(month(now()))).and(week(ORDER.CREATE_TIME).eq(week(now())));
                break;
        }
    }

    /**
     * 菜品销量排行
     * @param orderBy
     * @return
     */
    public List<RankVO> getDishRank(Integer orderBy) {

        QueryWrapper qw = QueryWrapper.create().select(DISH.NAME.as("name"),sum(CART.PRICE).as("total_custom"), sum(CART.QUANTITY).as("total_quantity")).from(ORDER)
                .leftJoin(ORDER_CART_RELATION).on(ORDER.ID.eq(ORDER_CART_RELATION.ORDER_ID))
                .leftJoin(CART).on(CART.ID.eq(ORDER_CART_RELATION.CART_ID))
                .leftJoin(DISH).on(DISH.ID.eq(CART.DISH_ID))
                .where(ORDER.STATUS.eq(OrderEnum.ORDER_STATUS_FINISHED.getIntValue()))
                .and(CART.STATUS.eq(OrderEnum.CART_STATUS_BUY.getIntValue()))
                .and(DISH.STATUS.eq(CommonEnum.STATUS_ON.getIntValue()))
                .groupBy(DISH.ID).orderBy(column("total_custom"),false);
        orderByType(orderBy,qw);
        List<Row> rows = Db.selectListByQuery(qw);
        List<RankVO> list = RowUtil.toObjectList(rows, RankVO.class);
        return list;
    }

    /**
     * 统计菜品，口味销量销售排行
     * @param orderBy
     * @return
     */
    public List<TasteRankVo> getTasteRank(Integer orderBy) {

        QueryWrapper qw = QueryWrapper.create().select(TASTE.NAME.as("name"),DISH.NAME.as("dish_name"),sum(CART.PRICE).as("total_custom"), sum(CART.QUANTITY).as("total_quantity")).from(ORDER)
                .leftJoin(ORDER_CART_RELATION).on(ORDER.ID.eq(ORDER_CART_RELATION.ORDER_ID))
                .leftJoin(CART).on(CART.ID.eq(ORDER_CART_RELATION.CART_ID))
                .leftJoin(DISH).on(DISH.ID.eq(CART.DISH_ID))
                .leftJoin(TASTE).on(TASTE.ID.eq(CART.TASTE_ID))
                .where(ORDER.STATUS.eq(OrderEnum.ORDER_STATUS_FINISHED.getIntValue()))
                .and(CART.STATUS.eq(OrderEnum.CART_STATUS_BUY.getIntValue()))
                .and(DISH.STATUS.eq(CommonEnum.STATUS_ON.getIntValue()))
                .and(TASTE.STATUS.eq(CommonEnum.STATUS_ON.getIntValue()))
                .groupBy(DISH.ID,TASTE.ID).orderBy(column("total_custom"),false);
        orderByType(orderBy,qw);

        List<Row> rows = Db.selectListByQuery(qw);
        List<TasteRankVo> list = RowUtil.toObjectList(rows, TasteRankVo.class);
        return list;
    }

    /**
     * 月份统计
     * @return
     */
    public StatVo getMonthStat() {
        //构造月份列表
        YearMonth end = YearMonth.now();
        YearMonth start = ChronoUnit.MONTHS.addTo(end, -5);
        long size = ChronoUnit.MONTHS.between(start, end) + 1;
        List<String> monthList = Stream.iterate(start, month -> month.plusMonths(1)).limit(size).map(YearMonth::toString).collect(Collectors.toList());
        List<Integer> quantityList = new ArrayList<>();
        List<BigDecimal> customList = new ArrayList<>();
        StatVo stat = new StatVo();
        for (String month : monthList) {
            QueryWrapper qw = QueryWrapper.create().select(
                ifNull(sum(ORDER.TOTAL_QUANTITY),column("0")).as("total_quantity"),
                ifNull(sum(ORDER.TOTAL_PRICE),column("0")).as("total_custom")).
            from(ORDER).where(ORDER.STATUS.eq(OrderEnum.ORDER_STATUS_FINISHED.getIntValue()))
                .and(dateFormat(ORDER.CREATE_TIME,"%Y-%m").eq(month));
            Row row = Db.selectOneByQuery(qw);
            RankVO object = RowUtil.toObject(row, RankVO.class);
            quantityList.add(object.getTotalQuantity());
            customList.add(object.getTotalCustom());
        }
        stat.setCustomList(customList);
        stat.setMonthList(monthList);
        stat.setQuantityList(quantityList);
        return stat;
    }

    /**
     * 前七天统计
     * @return
     */
    public StatVo getWeekStat() {
        LocalDate end = LocalDate.now();
        LocalDate start = ChronoUnit.DAYS.addTo(end, -6);
        long size = ChronoUnit.DAYS.between(start, end) + 1;
        List<String> weekList = Stream.iterate(start, day -> day.plusDays(1)).limit(size).map(LocalDate::toString).collect(Collectors.toList());
        List<Integer> quantityList = new ArrayList<>();
        List<BigDecimal> customList = new ArrayList<>();
        StatVo stat = new StatVo();
        for (String weekDay : weekList) {
            QueryWrapper qw = QueryWrapper.create().select(
                            ifNull(sum(ORDER.TOTAL_QUANTITY),column("0")).as("total_quantity"),
                            ifNull(sum(ORDER.TOTAL_PRICE),column("0")).as("total_custom")).
                    from(ORDER).where(ORDER.STATUS.eq(OrderEnum.ORDER_STATUS_FINISHED.getIntValue()))
                    .and(dateFormat(ORDER.CREATE_TIME,"%Y-%m-%d").eq(weekDay));
            Row row = Db.selectOneByQuery(qw);
            RankVO object = RowUtil.toObject(row, RankVO.class);
            quantityList.add(object.getTotalQuantity());
            customList.add(object.getTotalCustom());
        }
        stat.setCustomList(customList);
        stat.setWeekList(weekList);
        stat.setQuantityList(quantityList);
        return stat;
    }

    /**
     * 价格区间排行统计
     */
    public List<PriceRangeVo> getPriceRangeRank(Integer orderBy) {
        List<Integer> priceRange = Arrays.asList(0,5,8,12,15,18,20);
        List<PriceRangeVo> list = new ArrayList<>();
        for (int i = 0; i < priceRange.size()-1; i++) {
            Integer start = priceRange.get(i);
            Integer end = priceRange.get(i+1);
            QueryWrapper qw = QueryWrapper.create()
                    .select(ifNull(sum(CART.QUANTITY),column("0")).as("total_quantity")
                            ,ifNull(sum(CART.PRICE),column("0")).as("total_custom"))
                    .from(ORDER)
                    .leftJoin(ORDER_CART_RELATION).on(ORDER.ID.eq(ORDER_CART_RELATION.ORDER_ID))
                    .leftJoin(CART).on(CART.ID.eq(ORDER_CART_RELATION.CART_ID))
                    .leftJoin(DISH).on(DISH.ID.eq(CART.DISH_ID))
                    .leftJoin(TASTE).on(TASTE.ID.eq(CART.TASTE_ID))
                    .where(ORDER.STATUS.eq(OrderEnum.ORDER_STATUS_FINISHED.getIntValue()))
                    .and(CART.STATUS.eq(OrderEnum.CART_STATUS_BUY.getIntValue()))
                    .and(DISH.STATUS.eq(CommonEnum.STATUS_ON.getIntValue()))
                    .and(TASTE.STATUS.eq(CommonEnum.STATUS_ON.getIntValue()))
                    .and(DISH.PRICE.add(TASTE.PRICE).le(end))
                    .and(DISH.PRICE.add(TASTE.PRICE).gt(start));
            orderByType(orderBy,qw);
            Row row = Db.selectOneByQuery(qw);
            PriceRangeVo priceRangeVo = RowUtil.toObject(row, PriceRangeVo.class);
            priceRangeVo.setName("("+start+","+end+"]");
            list.add(priceRangeVo);
        }
        return list;
    }
}
